package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
  /**
   * 
   *  @author kobi
   *  
 * implement all StudentQuery  handler
 */
public class StudentQuery extends Query{

	public StudentQuery() {
		super();
	}
	/**
	 * 
	 * @param idUser
	 * @return Student
	 * @throws SQLException
	 */
	public Student getStudent(int idUser) throws SQLException{
		User user = new UserQuery().getUserByIdUser(idUser);
		setQuery("SELECT * FROM student WHERE iduser='"+idUser+"'");
		ResultSet rs = execQuery();
		if (rs.next()) return new Student(user, rs.getInt(2));
		else return null;
			
	}
	/**
	 * 
	 * @return array list of students
	 * @throws SQLException
	 */
	public ArrayList<Student> getAllStudents() throws SQLException{
		ArrayList<Student> array = new ArrayList<Student>();
		setQuery("SELECT user.*, student.idClass " +
				"FROM user JOIN student " +
				"ON user.iduser=student.iduser " +
				"GROUP BY user.lastName, user.firstName ");
		ResultSet rs = execQuery();
		while (rs.next()){
			Student student = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
					rs.getInt(5), rs.getString(6),rs.getString(7), rs.getInt(8));
			array.add(student);
		}
		return array;
	}
	/**
	 * remove student from table 
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Student> getUnAssignStudents() throws SQLException{
		ArrayList<Student> array = new ArrayList<Student>();
		setQuery("SELECT * " +
				"FROM user, student " +
				"WHERE user.iduser=student.iduser and student.idClass='0'" +
				"GROUP BY user.lastName, user.firstName");
		ResultSet rs = execQuery();
		while (rs.next()){
			Student student = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
					rs.getInt(5), rs.getString(6),rs.getString(7), rs.getInt(9));
			array.add(student);
		}
		return array;
	}
		/**
		 * assign student to class
		 * @param idStudent
		 * @param idClass
		 * @return
		 */
	public int assginStudent(int idStudent, int idClass){
		setQuery("UPDATE student SET idClass=? WHERE iduser=?");
		try {
			PreparedStatement update = getPS();
			update.setInt(1, idClass);
			update.setInt(2, idStudent);
			update.executeUpdate();
			update.close();
			return 0;
		} 
		catch (SQLException e){ e.printStackTrace();}
		return 0;
	}
	/**
	 * remove student from table
	 * @param idStudent
	 * @return
	 */
	public int unAssginStudent(int idStudent) {
		try {
			int tmp=0;
			Student student = getStudent(idStudent);
			setQuery("UPDATE student SET idClass=? WHERE iduser=?");
			PreparedStatement update = getPS();
			update.setInt(1, tmp);
			update.setInt(2, idStudent);
			update.executeUpdate();
			update.close();
			return student.getIdClass();
		} 
		catch (SQLException e){ e.printStackTrace();}
		return 0;
	}

/**
 * 
 * @param student
 * @param idLesson
 * @return boolean
 * @throws SQLException
 */
	public boolean signStudentToLesson(Student student, int idLesson) throws SQLException{
		return new RegQuery().regStudentToLesson(student, idLesson);
	}
/**
 * 	
 * @return
 * @throws SQLException
 */
	public ArrayList<Student> getTable() throws SQLException{
		ArrayList<Student> array = new ArrayList<Student>();
		setQuery("SELECT user.iduser " +
				" FROM user, student WHERE user.iduser=student.iduser " +
				"GROUP BY user.lastname, user.firstname");
		ResultSet rs = execQuery();
		while (rs.next())
			array.add(getStudent(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @param idLesson
	 * @return studetn in lesson 
	 * @throws SQLException
	 */
	public ArrayList<Student> getStudentInLesson(int idLesson) throws SQLException{
		ArrayList<Student> students = new ArrayList<Student>();
		ArrayList<Reg> array = new RegQuery().getStudentRegToLesson(idLesson);
		for (Reg reg: array)
			students.add(getStudent(reg.getIdUser()));
		return students;
	}
	/**
	 * 
	 * @param idUser
	 * @return sem Semester
	 * @throws SQLException
	 */
	public ArrayList<Semester> getSemesters(int idUser) throws SQLException{
		setQuery("SELECT lesson.idsemester FROM lesson, reg "+
				"WHERE lesson.idlesson=reg.idlesson AND reg.iduser='"+idUser+"' " +
						"GROUP BY lesson.idsemester");
		ArrayList<Semester> array = new ArrayList<Semester>();
		ResultSet rs =execQuery();
		while (rs.next())
			array.add(new SemesterQuery().getSemester(rs.getInt(1)));
		return array;
	}
}
